﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using ESOL.Common.Entity;
using ESOL.Business.Mst;
using ESOL.Business.Pur;
using System.Data;
using System.IO;
using ESOL.Common;
using ESOL.Business.Sys;
using Suzsoft.Smart.Data;
using AutoMapper;
using ESOL.Business.Inv;

namespace ESOL.Services
{
    [ServiceBehavior(UseSynchronizationContext = false,
   ConcurrencyMode = ConcurrencyMode.Multiple,
   InstanceContextMode = InstanceContextMode.PerCall)]
    public class PurService : IPurService
    {
        public bool TestConnect()
        {
            return true;
        }

        //供应商
        public MstSupplierEntity AddPurSupplier(MstSupplierEntity supp)
        {
            if (supp == null)
                return null;
            supp.CreatedDate = DateTime.Now;
            supp.UpdatedDate = DateTime.Now;
            supp.Status = WfStatus.WFS10.ToString();
            if (MstSupplierBs.InsertMstSupplier(supp))
                return supp;
            else return null;
        }

        public MstSupplierEntity UpdatePurSupplier(MstSupplierEntity supp)
        {
            if (supp == null)
                return null;
            supp.UpdatedDate = DateTime.Now;
            if (MstSupplierBs.UpdateMstSupplier(supp))
                return supp;
            else return null;
        }

        public int? DelPurSupplier(MstSupplierEntity supp)
        {
            if (supp == null)
                return 1;
            List<MstSupplierContactEntity> contacts = MstSupplierContactBs.GetSupplierContactAllListBySuppId(supp.Id);
            List<MstSupplierScopeEntity> scopes = DataAccess.Select<MstSupplierScopeEntity>(new MstSupplierScopeEntity { SupplierId = supp.Id });
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (contacts != null)
                        DataAccess.Delete(contacts, broker);
                    if (scopes != null)
                        DataAccess.Delete(scopes, broker);
                    DataAccess.Delete(supp, broker);
                    broker.Commit();
                    return null;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 2;
                }
            }
        }

        public int? SavePurSupplier(SupplierSaveParam param)
        {
            if (param == null)
                return 1;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    MstSupplierEntity sup = DataAccess.SelectSingle<MstSupplierEntity>(new MstSupplierEntity { Id = param.supp.Id });
                    if (sup == null)//新增
                    {
                        param.supp.Status = WfStatus.WFS10.ToString();
                        param.supp.CreatedBy = param.curUserNo;
                        param.supp.CreatedDate = DateTime.Now;
                        param.supp.UpdatedBy = param.curUserNo;
                        param.supp.UpdatedDate = DateTime.Now;
                        foreach (var contact in param.AddContacts)
                        {
                            contact.CreatedBy = param.curUserNo;
                            contact.CreatedDate = DateTime.Now;
                            contact.UpdatedBy = param.curUserNo;
                            contact.UpdatedDate = DateTime.Now;
                            contact.IsActive = 1;
                            contact.SupplierId = param.supp.Id;
                        }
                        broker.BeginTransaction();
                        DataAccess.Insert(param.supp, broker);
                        DataAccess.Insert(param.AddContacts, broker);
                        DataAccess.Insert(param.AddScopes, broker);
                        broker.Commit();
                        return null;
                    }
                    else//修改
                    {
                        param.supp.UpdatedBy = param.curUserNo;
                        param.supp.UpdatedDate = DateTime.Now;
                        foreach (var contact in param.AddContacts)
                        {
                            contact.CreatedBy = param.curUserNo;
                            contact.CreatedDate = DateTime.Now;
                            contact.UpdatedBy = param.curUserNo;
                            contact.UpdatedDate = DateTime.Now;
                            contact.IsActive = 1;
                            contact.SupplierId = param.supp.Id;
                        }
                        broker.BeginTransaction();
                        DataAccess.Update(param.supp, broker);
                        DataAccess.Delete(param.DelContacts, broker);
                        DataAccess.Update(param.UpdatedContacts, broker);
                        DataAccess.Insert(param.AddContacts, broker);

                        DataAccess.Delete(param.DelScopes, broker);
                        DataAccess.Update(param.UpdatedScopes, broker);
                        DataAccess.Insert(param.AddScopes, broker);
                        broker.Commit();
                        return null;
                    }
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 2;
                }
            }
        }

        public List<MstSupplierAttachedEntity> UploadPurSupplierAtt(List<AttachUploadMessage> source)
        {
            if (source == null)
                return null;
            if (source.Count == 0)
                return null;
            List<MstSupplierAttachedEntity> result = new List<MstSupplierAttachedEntity>();
            foreach (var request in source)
            {
                var sysStFile = SysStFileBs.GetSysStFile(new SysStFileEntity { Type = request.FormName });
                string uploadFolder = sysStFile.Path;
                string savaPath = @"Photo\";
                string dateString = DateTime.Now.ToShortDateString() + @"\";
                string fileName = request.ID + "_" + request.FileName;
                Stream sourceStream = request.FileData;
                FileStream targetStream = null;

                if (!sourceStream.CanRead)
                {
                    return null;
                }
                if (!savaPath.EndsWith("\\")) savaPath += "\\";
                uploadFolder = uploadFolder + savaPath + dateString;
                if (!Directory.Exists(uploadFolder))
                {
                    Directory.CreateDirectory(uploadFolder);
                }

                string filePath = Path.Combine(uploadFolder, fileName);
                using (targetStream = new FileStream(filePath, FileMode.Create, FileAccess.Write, FileShare.None))
                {
                    const int bufferLen = 4096;
                    byte[] buffer = new byte[bufferLen];
                    int count = 0;
                    while ((count = sourceStream.Read(buffer, 0, bufferLen)) > 0)
                    {
                        targetStream.Write(buffer, 0, count);
                    }
                    targetStream.Close();
                    sourceStream.Close();
                }

                MstSupplierAttachedEntity att = new MstSupplierAttachedEntity();
                att.Id = request.ID;
                att.SupplierId = request.RowID;
                att.AttachedType = "";
                att.FileName = request.FileName;
                att.FilePath = filePath;
                att.UploadBy = request.UploadBy;
                att.UploadDate = DateTime.Now;
                result.Add(att);
            }
            if (MstSupplierAttachedBs.InsertMstSupplierAttached(result))
            {
                return result;
            }
            else
            {
                return null;
            }
        }

        public int? DelPurSupplierContact(MstSupplierContactEntity contact)
        {
            if (contact == null)
                return 1;
            if (MstSupplierContactBs.DeleteMstSupplierContact(contact))
                return null;
            else return 2;
        }

        public int? DelPurSupplierAtt(MstSupplierAttachedEntity att)
        {
            if (att == null)
                return 1;
            if (MstSupplierAttachedBs.DeleteMstSupplierAttached(att))
            {
                File.Delete(att.FilePath);
                return null;
            }
            else return 2;
        }

        public MstSupplierEntity GetPurSupplierByKey(string supplierId)
        {
            if (string.IsNullOrEmpty(supplierId))
                return null;
            return MstSupplierBs.GetMstSupplier(new MstSupplierEntity { Id = supplierId });

        }

        public List<MstSupplierContactEntity> GetPurSupplierContactAllListBySuppId(string supplierId)
        {
            if (string.IsNullOrEmpty(supplierId))
                return null;
            return MstSupplierContactBs.GetSupplierContactAllListBySuppId(supplierId);

        }

        public List<MstSupplierAttachedEntity> GetPurSupplierAttAllListBySuppId(string supplierId)
        {
            if (string.IsNullOrEmpty(supplierId))
                return null;
            return MstSupplierAttachedBs.GetSupplierAttachedAllListBySuppId(supplierId);

        }

        public SupplierQueryPageResult GetPurSupplierAllListByCondition(SupplierQueryCondition con)
        {
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";

            condition += " and supp.IS_ACTIVE=" + con.IsActive;
            if (!string.IsNullOrEmpty(con.SupplierName))
            {
                condition += " and supp.SUPPLIER_NAME like '%" + con.SupplierName + "%'";
            }
            if (!string.IsNullOrEmpty(con.SupplierCode))
            {
                condition += " and supp.SUPPLIER_CODE like '%" + con.SupplierCode + "%'";
            }
            if (con.SupplierType != null)
            {
                condition += " and supp.SUPPLIER_TYPE='" + con.SupplierType + "'";
            }
            if (con.Status != null)
            {
                condition += " and supp.STATUS='" + con.Status + "'";
            }
            DataSet ds = MstSupplierBs.GetAllDsByCondition(pageIndex, pageSize, condition);
            if (ds == null)
                return null;
            List<MstSupplierEntity> result = new List<MstSupplierEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                MstSupplierEntity app = new MstSupplierEntity(dr);
                result.Add(app);
            }
            SupplierQueryPageResult p = new SupplierQueryPageResult();
            if (ds.Tables[0].Rows.Count == 0)
                p.TotalCount = 0;
            else p.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            p.Supplieres = result;
            return p;
        }

        //采购申请：工作流

        public PurAppEntity AddPurApp(PurAppEntity app)
        {
            if (app == null)
                return null;
            app.CreatedDate = DateTime.Now;
            app.UpdatedDate = DateTime.Now;
            if (PurAppBs.InsertPurApp(app))
                return app;
            else return null;
        }

        public PurAppEntity UpdatePurApp(PurAppEntity app)
        {
            if (app == null)
                return null;
            app.UpdatedDate = DateTime.Now;
            if (PurAppBs.UpdatePurApp(app))
                return app;
            else return null;
        }

        public PurAppEntity UpdatePurAppAndItem(PurAppEntity app, List<PurAppItemEntity> appItems)
        {
            if (app == null)
                return null;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (appItems != null)
                    {
                        DataAccess.Update(appItems, broker);
                    }
                    app.UpdatedDate = DateTime.Now;
                    DataAccess.Update(app, broker);
                    broker.Commit();
                    return app;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return null;
                }
            }
        }

        public int? DelPurApp(PurAppEntity app)
        {
            if (app == null)
                return 1;
            List<PurAppItemEntity> items = PurAppItemBs.GetPurAppItemAllListByAppId(app.Id);
            if (PurAppBs.DeletePurApp(app, items))
                return null;
            else return 2;
        }

        public int? DelPurAppItem(PurAppItemEntity appItem)
        {
            if (appItem == null)
                return 1;
            var item = PurAppItemBs.GetPurAppItem(new PurAppItemEntity { Id = appItem.Id });
            if (item == null)
                return null;
            if (PurAppItemBs.DeletePurAppItem(item))
                return null;
            else return 2;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="app"></param>
        /// <param name="items"></param>
        /// <returns></returns>
        public int? SavePurApp(string userNo, PurAppEntity app, List<PurAppItemEntity> items)
        {
            if (app == null)
                return 1;
            PurAppEntity t = PurAppBs.GetPurApp(new PurAppEntity { Id = app.Id });
            if (t == null)//新增
            {
                app.CreatedDate = DateTime.Now;
                app.CreatedBy = userNo;
                app.UpdatedDate = DateTime.Now;
                app.UpdatedBy = userNo;
                app.Status = Common.WfStatus.WFS10.ToString();
                app.AppNo = MstCodeRuleBs.GetCurrentOrderCode("PurchaseApplicationOrder");
                if (items != null)
                {
                    foreach (var item in items)
                    {
                        item.PurAppId = app.Id;
                    }
                }
                if (PurAppBs.AddPurApp(app, items))
                {
                    return null;
                }
                {
                    return 2;
                }
            }
            else//修改
            {
                app.UpdatedDate = DateTime.Now;
                app.UpdatedBy = userNo;
                List<PurAppItemEntity> adds = new List<PurAppItemEntity>();
                List<PurAppItemEntity> updates = new List<PurAppItemEntity>();
                foreach (var item in items)
                {
                    PurAppItemEntity i = PurAppItemBs.GetPurAppItem(new PurAppItemEntity { Id = item.Id });
                    if (i == null)
                    {
                        item.PurAppId = app.Id;
                        adds.Add(item);
                    }
                    else
                    {
                        updates.Add(item);
                    }
                }
                if (PurAppBs.UpdatePurApp(app, adds, updates))
                {
                    return null;
                }
                {
                    return 3;
                }
            }
        }

        public PurAppEntity GetPurAppByKey(string purAppId)
        {
            if (string.IsNullOrEmpty(purAppId))
                return null;
            return PurAppBs.GetPurApp(new PurAppEntity { Id = purAppId });
        }

        public List<PurAppItemEntity> GetPurAppItemByAppId(string purAppId)
        {
            if (string.IsNullOrEmpty(purAppId))
                return null;
            return PurAppItemBs.GetPurAppItemAllListByAppId(purAppId);
        }

        public PurAppQueryPageResult GetPurAppByCondition(AppQueryCondition con)
        {
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.CreatedBy))
            {
                condition += " and app.CREATED_BY='" + con.CreatedBy + "'";
            }
            else if (!string.IsNullOrEmpty(con.CreatedUser))
            {
                condition += " and u1.firstName like '%" + con.CreatedUser + "%'";
            }
            if (con.IsActive != null)
            {
                condition += " and app.IS_ACTIVE=" + con.IsActive;
            }
            if (!string.IsNullOrEmpty(con.AppNo))
            {
                condition += " and app.APP_NO='" + con.AppNo + "'";
            }
            if (con.PurchaseType != null)
            {
                condition += " and app.PURCHASE_TYPE='" + con.PurchaseType + "'";
            }
            if (con.PurchaseType != null)
            {
                condition += " and app.APP_PERSON='" + con.AppPerson + "'";
            }
            if (con.AppDateFrom != null && con.AppDateFrom != new DateTime(1, 1, 1))
            {
                condition += " and app.APP_DATE>='" + con.AppDateFrom.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (con.AppDateTo != null && con.AppDateTo != new DateTime(1, 1, 1))
            {
                condition += " and app.APP_DATE<='" + con.AppDateTo.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (con.RelatedType != null)
            {
                condition += " and app.RELATED_TYPE='" + con.RelatedType + "'";
            }
            if (con.RelatedNo != null)
            {
                condition += " and app.RELATED_NO='" + con.RelatedNo + "'";
            }
            if (con.Status != null)
            {
                condition += " and app.STATUS='" + con.Status + "'";
            }
            DataSet ds = PurAppBs.GetAllDsByCondition(pageIndex, pageSize, condition);
            if (ds == null)
                return null;
            List<PurAppEntity> result = new List<PurAppEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                PurAppEntity app = new PurAppEntity(dr);
                result.Add(app);
            }
            PurAppQueryPageResult p = new PurAppQueryPageResult();
            if (ds.Tables[0].Rows.Count == 0)
                p.TotalCount = 0;
            else p.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            p.Apps = result;
            return p;
        }

        /// <summary>
        /// 采购计划：分页查询采购明细
        /// </summary>
        /// <param name="con"></param>
        /// <returns></returns>
        public PurAppItemQueryPageResult GetPurAppItemAllListByCondition(PurAppItemQueryCondition con)
        {
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.PurAppId))
            {
                condition += " and app.ID='" + con.PurAppId + "'";
            }
            else if (!string.IsNullOrEmpty(con.PurAppNo))
            {
                condition += " and app.APP_NO like '%" + con.PurAppNo + "%'";
            }
            if (!string.IsNullOrEmpty(con.PurAppPerson))
            {
                condition += " and app.APP_PERSON='" + con.PurAppPerson + "'";
            }
            if (!string.IsNullOrEmpty(con.PurAppItemStatus))
            {
                condition += " and appi.STATUS='" + con.PurAppItemStatus + "'";
            }
            if (con.RequiredDateFrom != null && con.RequiredDateFrom != new DateTime(1, 1, 1))
            {
                condition += " and appi.REQUIRED_DATE>='" + con.RequiredDateFrom.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (con.RequiredDateTo != null && con.RequiredDateTo != new DateTime(1, 1, 1))
            {
                condition += " and appi.REQUIRED_DATE<='" + con.RequiredDateTo.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (!string.IsNullOrEmpty(con.PurMaterialId))
            {
                condition += " and appi.PRODUCT_ID='" + con.PurMaterialId + "'";
            }
            if (!string.IsNullOrEmpty(con.PurMaterialName))
            {
                condition += " and appi.PRODUCT_NAME like '%" + con.PurMaterialName + "%'";
            }
            if (!string.IsNullOrEmpty(con.PurPoStatus))
            {
                condition += " and app.STATUS='" + con.PurPoStatus + "'";
            }
            string sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t)";
            string sqlSource = @"select appi.*,app.APP_NO,tol.CUR_TOTAL
from PUR_APP app
left join PUR_APP_ITEM appi on app.ID=appi.PUR_APP_ID
left join V_INV_TOTAL tol on appi.PRODUCT_ID=tol.PRODUCT_ID
where 1=1 ";
            sqlSource += condition;
            sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            PurAppItemQueryPageResult result = new PurAppItemQueryPageResult();
            List<PurMaintainAppItem> appItems = new List<PurMaintainAppItem>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                PurMaintainAppItem appItem = new PurMaintainAppItem();
                appItem.Id = dr["ID"].ToString();
                appItem.PurAppId = dr["PUR_APP_ID"].ToString();
                appItem.PurAppNo = dr["APP_NO"].ToString();
                appItem.ProductId = dr["PRODUCT_ID"].ToString();
                appItem.ProductName = dr["PRODUCT_NAME"].ToString();
                appItem.ProductCode = dr["PRODUCT_CODE"].ToString();
                if (dr["QTY"] != DBNull.Value)
                {
                    appItem.Qty = Convert.ToDecimal(dr["QTY"].ToString());
                }
                if (dr["CUR_TOTAL"] != DBNull.Value)
                {
                    appItem.CurStgQty = Convert.ToDecimal(dr["CUR_TOTAL"].ToString());
                }
                appItem.Unit = dr["UNIT"].ToString();
                if (dr["PRICE"] != DBNull.Value)
                {
                    appItem.Price = Convert.ToDecimal(dr["PRICE"].ToString());
                }
                if (dr["REQUIRED_DATE"] != DBNull.Value)
                {
                    appItem.RequiredDate = (DateTime)dr["REQUIRED_DATE"];
                }
                appItem.Remark = dr["REMARK"].ToString();
                appItem.Status = dr["STATUS"].ToString();
                appItems.Add(appItem);
            }
            result.AppItems = appItems;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;
        }

        //采购询价单

        public MergePurAppItemResult MergePurAppItem(List<PurMaintainAppItem> items)
        {
            if (items == null)
                return null;
            List<PurRfqItemEntity> rItems = new List<PurRfqItemEntity>();
            List<PurRlAppiRfqiEntity> rels = new List<PurRlAppiRfqiEntity>();
            foreach (var item in items)
            {
                var rItem = rItems.Find(a => a.MaterialId == item.ProductId);
                if (rItem == null)
                {
                    rItem = new PurRfqItemEntity();
                    rItem.Id = Guid.NewGuid().ToString();
                    rItem.Qty = item.Qty;
                    rItem.MaterialId = item.ProductId;
                    rItem.MaterialName = item.ProductName;
                    rItem.MaterialCode = item.ProductCode;
                    rItem.Unit = item.Unit;
                    rItems.Add(rItem);
                }
                else
                {
                    rItem.Qty += item.Qty;
                }

                PurRlAppiRfqiEntity rel = new PurRlAppiRfqiEntity();
                rel.PurAppiId = item.Id;
                rel.PurRfqiId = rItem.Id;
                rels.Add(rel);
            }
            MergePurAppItemResult mergeResult = new MergePurAppItemResult();
            mergeResult.rfqItems = rItems;
            mergeResult.rels = rels;
            return mergeResult;
        }

        public PurRfqEntity AddPurRfq(PurRfqEntity rfq)
        {
            if (rfq == null)
                return null;
            rfq.CreatedDate = DateTime.Now;
            rfq.UpdatedDate = DateTime.Now;
            if (PurRfqBs.InsertPurRfq(rfq))
                return rfq;
            else return null;
        }

        public PurRfqEntity UpdatePurRfq(PurRfqEntity rfq)
        {
            if (rfq == null)
                return null;
            rfq.UpdatedDate = DateTime.Now;
            if (PurRfqBs.UpdatePurRfq(rfq))
                return rfq;
            else return null;
        }

        public int? DelPurRfq(PurRfqEntity rfq)
        {
            if (rfq == null)
                return 1;
            var rfqItems = PurRfqItemBs.GetRfqItemAllListByRfqNo(rfq.RfqNo);
            if (PurRfqBs.DeletePurRfq(rfq, rfqItems))
                return null;
            else return 2;
        }

        public int? DelPurRfqItem(PurRfqItemEntity item)
        {
            if (item == null)
                return 1;
            var rels = PurAppiRfqiBs.GetRelAllListByRfqItemId(item.Id);
            if (PurRfqItemBs.DeletePurRfqItem(item, rels))
                return null;
            else return 2;
        }

        public int? SavePurRfq(PurRfqEntity rfq, List<PurRfqItemEntity> items, List<PurRlAppiRfqiEntity> rels)
        {
            if (rfq == null)
                return 1;
            PurRfqEntity t = PurRfqBs.GetPurRfq(new PurRfqEntity { Id = rfq.Id });
            if (t == null)//新增
            {
                rfq.CreatedDate = DateTime.Now;
                rfq.UpdatedDate = DateTime.Now;
                rfq.RfqNo = MstCodeRuleBs.GetCurrentOrderCode("PurRfqCode");
                if (items != null)
                {
                    foreach (var item in items)
                    {
                        item.RfqNo = rfq.RfqNo;
                    }
                }
                List<PurAppItemEntity> appItems = null;
                if (rels != null)
                {
                    appItems = new List<PurAppItemEntity>();
                    foreach (var rel in rels)
                    {
                        PurAppItemEntity appItem = PurAppItemBs.GetPurAppItem(new PurAppItemEntity { Id = rel.PurAppiId });
                        appItem.Status = Common.PurchaseAppItemStatus.PUR_ITEM20.ToString();
                        appItems.Add(appItem);
                    }
                }
                using (DataAccessBroker broker = DataAccessFactory.Instance())
                {
                    try
                    {
                        broker.BeginTransaction();
                        DataAccess.Insert(rfq, broker);
                        if (items != null)
                            DataAccess.Insert(items, broker);
                        if (rels != null)
                            DataAccess.Insert(rels, broker);
                        if (appItems != null)
                            DataAccess.Update(appItems, broker);
                        broker.Commit();
                        return null;
                    }
                    catch (Exception ex)
                    {
                        broker.RollBack();
                        return 2;
                    }
                }
            }
            else//修改
            {
                List<PurRfqItemEntity> adds = new List<PurRfqItemEntity>();
                List<PurRfqItemEntity> updates = new List<PurRfqItemEntity>();
                foreach (var item in items)
                {
                    PurRfqItemEntity i = PurRfqItemBs.GetPurRfqItem(new PurRfqItemEntity { Id = item.Id });
                    if (i == null)
                    {
                        i.RfqNo = rfq.RfqNo;
                        adds.Add(i);
                    }
                    else
                    {
                        updates.Add(i);
                    }
                }
                if (PurRfqBs.UpdatePurRfq(rfq, adds, updates))
                {
                    return null;
                }
                {
                    return 3;
                }
            }
        }

        public PurRfqEntity GetPurMaintainRfqByKey(string purRfqId)
        {
            if (string.IsNullOrEmpty(purRfqId))
                return null;
            return PurRfqBs.GetPurRfq(new PurRfqEntity { Id = purRfqId });
        }

        public List<PurRfqItemEntity> GetPurRfqItemByRfqNo(string rfqNo)
        {
            if (string.IsNullOrEmpty(rfqNo))
                return null;
            return PurRfqItemBs.GetRfqItemAllListByRfqNo(rfqNo);
        }

        public PurRfqQueryPageResult GetPurRfqAllListByCondition(RfqQueryCondition con)
        {
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.CreatedBy))
            {
                condition += " and rfq.CREATED_BY='" + con.CreatedBy + "'";
            }
            else if (!string.IsNullOrEmpty(con.CreatedUser))
            {
                condition += " and u1.firstName like '%" + con.CreatedUser + "%'";
            }
            if (con.IsActive != null)
            {
                condition += " and rfq.IS_ACTIVE=" + con.IsActive;
            }
            if (!string.IsNullOrEmpty(con.RfqNo))
            {
                condition += " and rfq.RFQ_NO='" + con.RfqNo + "'";
            }
            if (con.ReqQuDateFrom != null && con.ReqQuDateFrom != new DateTime(1, 1, 1))
            {
                condition += " and rfq.REQ_QUOTE_DATE>='" + con.ReqQuDateFrom.ToLongTimeString() + "'";
            }
            if (con.ReqQuDateTo != null && con.ReqQuDateTo != new DateTime(1, 1, 1))
            {
                condition += " and rfq.REQ_QUOTE_DATE<='" + con.ReqQuDateTo.ToLongTimeString() + "'";
            }
            if (con.Status != null)
            {
                condition += " and rfq.STATUS='" + con.Status + "'";
            }
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from PUR_RFQ) as cnt
from PUR_RFQ
where id not in (
    select top ({1}*({2}-1)) id from PUR_RFQ order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select rfq.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) rfq
left join BPMDB.dbo.PW_User u1 on rfq.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on rfq.CREATED_BY=u2.userNo";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select rfq.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from PUR_RFQ rfq
left join BPMDB.dbo.PW_User u1 on rfq.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on rfq.CREATED_BY=u2.userNo
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            List<PurRfqEntity> rfqs = new List<PurRfqEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                PurRfqEntity rfq = new PurRfqEntity(dr);
                rfqs.Add(rfq);
            }
            PurRfqQueryPageResult result = new PurRfqQueryPageResult();
            result.rfqs = rfqs;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;
        }

        //采购报价单

        public PurQuotationEntity AddPurQu(PurQuotationEntity qu)
        {
            if (qu == null)
                return null;
            qu.CreatedDate = DateTime.Now;
            qu.UpdatedDate = DateTime.Now;
            if (PurQuotationBs.InsertPurQuotation(qu))
                return qu;
            else return null;
        }

        public PurQuotationEntity UpdatePurQu(PurQuotationEntity qu)
        {
            if (qu == null)
                return null;
            qu.UpdatedDate = DateTime.Now;
            if (PurQuotationBs.UpdatePurQuotation(qu))
                return qu;
            else return null;
        }

        public int? DelPurQu(PurQuotationEntity qu)
        {
            if (qu == null)
                return 1;
            List<PurQuotationItemEntity> items = PurQuotationItemBs.GetQuItemAllListByQuNo(qu.QuNo);
            if (PurQuotationBs.DeletePurQuotation(qu, items))
                return null;
            else return 2;
        }

        public int? SavePurQu(PurQuotationEntity qu, List<PurQuotationItemEntity> items)
        {
            if (qu == null)
                return 1;
            PurQuotationEntity t = PurQuotationBs.GetPurQuotation(new PurQuotationEntity { Id = qu.Id });
            if (t == null)//新增
            {
                qu.CreatedDate = DateTime.Now;
                qu.UpdatedDate = DateTime.Now;
                qu.QuNo = MstCodeRuleBs.GetCurrentOrderCode("PurQuCode");
                if (items != null)
                {
                    foreach (var item in items)
                    {
                        item.QuNo = qu.QuNo;
                    }
                }
                if (PurQuotationBs.AddPurQu(qu, items))
                {
                    return null;
                }
                else
                {
                    return 2;
                }
            }
            else//修改
            {
                qu.UpdatedDate = DateTime.Now;
                List<PurQuotationItemEntity> adds = new List<PurQuotationItemEntity>();
                List<PurQuotationItemEntity> updates = new List<PurQuotationItemEntity>();
                foreach (var item in items)
                {
                    PurQuotationItemEntity i = PurQuotationItemBs.GetPurQuotationItem(new PurQuotationItemEntity { Id = item.Id });
                    if (i == null)
                    {
                        i.QuNo = qu.QuNo;
                        adds.Add(i);
                    }
                    else
                    {
                        updates.Add(i);
                    }
                }
                if (PurQuotationBs.UpdatePurQu(qu, adds, updates))
                {
                    return null;
                }
                {
                    return 3;
                }
            }
        }

        public PurQuotationEntity GetPurMaintainQuByKey(string purQuId)
        {
            if (string.IsNullOrEmpty(purQuId))
                return null;
            return PurQuotationBs.GetPurQuotation(new PurQuotationEntity { Id = purQuId });
        }

        public QuQueryPageResult GetPurMaintainQuByCondition(QuQueryCondition con)
        {
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.CreatedBy))
            {
                condition += " and qu.CREATED_BY='" + con.CreatedBy + "'";
            }
            else if (!string.IsNullOrEmpty(con.CreatedUser))
            {
                condition += " and u1.firstName like '%" + con.CreatedUser + "%'";
            }
            if (con.IsActive != null)
            {
                condition += " and qu.IS_ACTIVE=" + con.IsActive;
            }
            if (!string.IsNullOrEmpty(con.QuNo))
            {
                condition += " and qu.QU_NO='" + con.QuNo + "'";
            }
            if (!string.IsNullOrEmpty(con.RfqNo))
            {
                condition += " and qu.RFQ_NO='" + con.RfqNo + "'";
            }
            if (con.SupplierId != null)
            {
                condition += " and qu.SUPPLIER_ID='" + con.SupplierId + "'";
            }
            else if (con.SupplierName != null)
            {
                condition += " and qu.SUPPLIER_NAME like '%" + con.SupplierName + "%'";
            }
            if (con.QuDateFrom != null && con.QuDateFrom != new DateTime(1, 1, 1))
            {
                condition += " and qu.CREATED_DATE>='" + con.QuDateFrom.ToLongTimeString() + "'";
            }
            if (con.QuDateTo != null && con.QuDateTo != new DateTime(1, 1, 1))
            {
                condition += " and qu.CREATED_DATE<='" + con.QuDateTo.ToLongTimeString() + "'";
            }
            if (con.Status != null)
            {
                condition += " and qu.STATUS='" + con.Status + "'";
            }

            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from PUR_QUOTATION) as cnt
from PUR_QUOTATION
where id not in (
    select top ({1}*({2}-1)) id from PUR_QUOTATION order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select qu.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) qu
left join BPMDB.dbo.PW_User u1 on qu.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on qu.CREATED_BY=u2.userNo";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select qu.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from PUR_QUOTATION qu
left join BPMDB.dbo.PW_User u1 on qu.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on qu.CREATED_BY=u2.userNo
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);

            if (ds == null)
                return null;
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            List<PurQuotationEntity> qus = new List<PurQuotationEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                PurQuotationEntity qu = new PurQuotationEntity(dr);
                qus.Add(qu);
            }
            QuQueryPageResult result = new QuQueryPageResult();
            result.Qus = qus;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;
        }

        public List<PurQuotationItemEntity> GetPurMaintainQuItemAllListByQuNo(string quNo)
        {
            if (string.IsNullOrEmpty(quNo))
                return null;
            return PurQuotationItemBs.GetQuItemAllListByQuNo(quNo);
        }

        //采购订单：工作流

        /// <summary>
        /// 根据采购申请明细，产生采购订单
        /// 回写采购申请明细status为：已下单
        /// </summary>
        /// <param name="items"></param>
        /// <returns></returns>
        public TransPurAppItemResult CreatePurPoByApp(List<PurMaintainAppItem> items)
        {
            if (items == null)
                return null;
            List<PurPoItemEntity> pItems = new List<PurPoItemEntity>();
            List<PurRlAppiPoiEntity> rels = new List<PurRlAppiPoiEntity>();
            foreach (var item in items)
            {
                var pItem = pItems.Find(a => a.MaterialId == item.ProductId);
                if (pItem == null)
                {
                    pItem = new PurPoItemEntity();
                    pItem.Id = Guid.NewGuid().ToString();
                    pItem.MaterialId = item.ProductId;
                    pItem.MaterialName = item.ProductName;
                    pItem.MaterialCode = item.ProductCode;
                    pItem.Qty = item.Qty;
                    pItem.Unit = item.Unit;
                    pItems.Add(pItem);
                }
                else
                {
                    pItem.Qty += item.Qty;
                }

                PurRlAppiPoiEntity rel = new PurRlAppiPoiEntity();
                rel.PurAppiId = item.Id;
                rel.PurPoiId = pItem.Id;
                rels.Add(rel);
            }
            TransPurAppItemResult result = new TransPurAppItemResult();
            result.items = pItems;
            result.rels = rels;
            return result;

        }

        /// <summary>
        /// 根据采购报检单，产生采购订单
        /// 回写采购申请明细status为：已下单
        /// </summary>
        /// <param name="items"></param>
        /// <returns></returns>
        public TransPurAppItemResult CreatePurPoByQu(List<PurQuotationEntity> qus)
        {
            if (qus == null)
                return null;
            List<PurPoItemEntity> pItems = new List<PurPoItemEntity>();
            PurPoEntity po = new PurPoEntity();
            po.Id = Guid.NewGuid().ToString();
            po.IsActive = 1;
            po.Status = WfStatus.WFS10.ToString();
            foreach (var qu in qus)
            {
                List<PurQuotationItemEntity> items = PurQuotationItemBs.GetQuItemAllListByQuNo(qu.QuNo);
                if (items == null)
                    continue;
                foreach (var item in items)
                {
                    var pItem = pItems.Find(a => a.MaterialId == item.MaterialId);
                    if (pItem == null)
                    {
                        pItem = new PurPoItemEntity();
                        pItem.Id = Guid.NewGuid().ToString();
                        pItem.MaterialId = item.MaterialId;
                        pItem.MaterialName = item.MaterialName;
                        pItem.MaterialCode = item.MaterialCode;
                        pItem.Qty = item.Qty;
                        pItem.Unit = item.Unit;
                        pItems.Add(pItem);
                    }
                    else
                    {
                        pItem.Qty += item.Qty;
                    }
                }
            }
            TransPurAppItemResult result = new TransPurAppItemResult();
            result.items = pItems;
            return result;

        }

        public PurPoEntity AddPurPo(PurPoEntity po)
        {
            if (po == null)
                return null;
            po.CreatedDate = DateTime.Now;
            po.UpdatedDate = DateTime.Now;
            if (PurPoBs.InsertPurPo(po))
                return po;
            else return null;
        }

        public PurPoEntity UpdatePurPo(PurPoEntity po)
        {
            if (po == null)
                return null;
            po.UpdatedDate = DateTime.Now;
            if (PurPoBs.UpdatePurPo(po))
                return po;
            else return null;
        }

        public PurPoEntity WfEndUpdatePurPo(PurPoEntity po)
        {
            if (po == null)
                return null;
            po.UpdatedDate = DateTime.Now;
            po.Status = WfStatus.WFS30.ToString();
            List<PurPoItemEntity> poItems = PurPoItemBs.GetPoItemAllListByPoNo(po.PoNo);
            string sql = @"select distinct appi.* 
from PUR_RL_QUI_POI rlq
inner join PUR_QUOTATION_ITEM qui on rlq.PUR_QUI_ID=qui.ID
inner join PUR_RFQ_ITEM rfqi on qui.RFQ_ITEM_ID=rfqi.ID
inner join PUR_RL_APPI_RFQI rlr on rfqi.ID=rlr.PUR_RFQI_ID
inner join PUR_APP_ITEM appi on rlr.PUR_APPI_ID=appi.ID
where rlq.PUR_POI_ID in ({0})
union all
select distinct appi.*
from PUR_RL_APPI_POI rla
inner join PUR_APP_ITEM appi on rla.PUR_APPI_ID=appi.ID
where rla.PUR_POI_ID in ({0})";
            string con = "";
            foreach (var poItem in poItems)
            {
                con += "'" + poItem.Id + "',";
            }
            con = con.Substring(0, con.Length - 1);
            sql = string.Format(sql, con);
            DataSet ds = DataAccess.SelectDataSet(sql);
            List<PurAppItemEntity> appItems = null;
            if (ds.Tables[0].Rows.Count != 0)
            {
                appItems = new List<PurAppItemEntity>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    PurAppItemEntity appItem = new PurAppItemEntity(dr);
                    appItem.Status = Common.PurchaseAppItemStatus.PUR_ITEM30.ToString();
                    appItems.Add(appItem);
                }
            }
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    DataAccess.Update(po, broker);
                    if (appItems != null)
                        DataAccess.Update(appItems, broker);
                    broker.Commit();
                    return po;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return null;
                }
            }
        }

        public int? DelPurPo(PurPoEntity po)
        {
            if (po == null)
                return 1;
            List<PurPoItemEntity> items = PurPoItemBs.GetPoItemAllListByPoNo(po.PoNo);
            if (PurPoBs.DeletePurPo(po, items))
                return null;
            else return 2;
        }

        public int? SavePurPo(PurPoEntity po, List<PurPoItemEntity> items)
        {
            if (po == null)
                return 1;
            po.UpdatedDate = DateTime.Now;
            List<PurPoItemEntity> adds = new List<PurPoItemEntity>();
            List<PurPoItemEntity> updates = new List<PurPoItemEntity>();
            foreach (var item in items)
            {
                PurPoItemEntity i = PurPoItemBs.GetPurPoItem(new PurPoItemEntity { Id = item.Id });
                if (i == null)
                {
                    i.PoNo = po.PoNo;
                    adds.Add(i);
                }
                else
                {
                    updates.Add(i);
                }
            }
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    DataAccess.Update(po, broker);
                    if (adds.Count > 0)
                    {
                        DataAccess.Insert(adds, broker);
                    }
                    if (updates.Count > 0)
                    {
                        DataAccess.Update(updates, broker);
                    }
                    broker.Commit();
                    return null;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 2;
                }
            }
        }

        public int? AddPurPoForApp(PurPoEntity po, List<PurPoItemEntity> items, List<PurRlAppiPoiEntity> rels)
        {
            if (po == null || items == null || rels == null)
                return 1;
            po.CreatedDate = DateTime.Now;
            po.UpdatedDate = DateTime.Now;
            po.PoNo = MstCodeRuleBs.GetCurrentOrderCode("PurchaseOrder");
            po.Status = WfStatus.WFS10.ToString();
            foreach (var item in items)
            {
                item.PoNo = po.PoNo;
            }
            List<PurAppItemEntity> appItems = new List<PurAppItemEntity>();
            foreach (var rel in rels)
            {
                PurAppItemEntity appItem = PurAppItemBs.GetPurAppItem(new PurAppItemEntity { Id = rel.PurAppiId });
                if (appItem != null)
                {
                    appItem.Status = Common.PurchaseAppItemStatus.PUR_ITEM20.ToString();
                    appItems.Add(appItem);
                }
            }
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    DataAccess.Insert(items, broker);
                    DataAccess.Insert(rels, broker);
                    DataAccess.Insert(po, broker);
                    DataAccess.Update(appItems, broker);
                    broker.Commit();
                    return null;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 2;
                }
            }
        }

        public int? AddPurPoForQu(PurPoEntity po, List<PurPoItemEntity> items, List<PurRlQuiPoiEntity> rels)
        {
            if (po == null || items == null || rels == null)
                return 1;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    po.CreatedDate = DateTime.Now;
                    po.UpdatedDate = DateTime.Now;
                    po.Status = WfStatus.WFS10.ToString();
                    po.PoNo = MstCodeRuleBs.GetCurrentOrderCode("PurchaseOrder");
                    foreach (var item in items)
                    {
                        item.PoNo = po.PoNo;
                    }
                    DataAccess.Insert(items, broker);
                    DataAccess.Insert(rels, broker);
                    DataAccess.Insert(po, broker);
                    broker.Commit();
                    return null;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 2;
                }
            }
        }

        public int? AddPurPoForCustom(PurPoEntity po, List<PurPoItemEntity> items)
        {
            if (po == null || items == null)
                return 1;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    po.CreatedDate = DateTime.Now;
                    po.UpdatedDate = DateTime.Now;
                    po.Status = WfStatus.WFS10.ToString();
                    po.PoNo = MstCodeRuleBs.GetCurrentOrderCode("PurchaseOrder");
                    foreach (var item in items)
                    {
                        item.PoNo = po.PoNo;
                    }
                    DataAccess.Insert(items, broker);
                    DataAccess.Insert(po, broker);
                    broker.Commit();
                    return null;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 2;
                }
            }
        }

        public int? DelPurPoItem(PurPoItemEntity item)
        {
            if (item == null)
                return 1;
            var rlApp = DataAccess.Select<PurRlAppiPoiEntity>(new PurRlAppiPoiEntity { PurPoiId = item.Id });
            var rlQu = DataAccess.Select<PurRlQuiPoiEntity>(new PurRlQuiPoiEntity { PurPoiId = item.Id });
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (rlApp != null)
                        DataAccess.Delete(rlApp, broker);
                    if (rlQu != null)
                        DataAccess.Delete(rlQu, broker);
                    DataAccess.Delete(item, broker);
                    broker.Commit();
                    return null;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 2;
                }
            }
        }

        public PurPoEntity GetPurMaintainPoByKey(string purPoId)
        {
            if (string.IsNullOrEmpty(purPoId))
                return null;
            return PurPoBs.GetPurPo(new PurPoEntity { Id = purPoId });
        }

        public PoQueryPageResult GetPurMaintainPoByCondition(PoQueryCondition con)
        {
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.CreatedBy))
            {
                condition += " and po.CREATED_BY='" + con.CreatedBy + "'";
            }
            else if (!string.IsNullOrEmpty(con.CreatedUser))
            {
                condition += " and u1.firstName like '%" + con.CreatedUser + "%'";
            }
            if (con.IsActive != null)
            {
                condition += " and po.IS_ACTIVE=" + con.IsActive;
            }
            if (!string.IsNullOrEmpty(con.QuNo))
            {
                condition += " and po.QU_NO='" + con.QuNo + "'";
            }
            if (!string.IsNullOrEmpty(con.PoNo))
            {
                condition += " and po.PO_NO='" + con.PoNo + "'";
            }
            if (con.SupplierId != null)
            {
                condition += " and po.SUPPLIER_ID='" + con.SupplierId + "'";
            }
            else if (con.SupplierName != null)
            {
                condition += " and po.SUPPLIER_NAME like '%" + con.SupplierName + "%'";
            }
            if (con.MaterialId != null)
            {
                condition += " and poi.MATERIAL_ID='" + con.MaterialId + "'";
            }
            else if (con.MaterialName != null)
            {
                condition += " and poi.MATERIAL_NAME like '%" + con.MaterialName + "%'";
            }
            if (con.Status != null)
            {
                condition += " and po.STATUS='" + con.Status + "'";
            }

            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from PUR_PO) as cnt
from PUR_PO
where id not in (
    select top ({1}*({2}-1)) id from PUR_PO order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select distinct po.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) po
left join PUR_PO_ITEM poi on po.PO_NO=poi.PO_NO
left join BPMDB.dbo.PW_User u1 on po.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on po.CREATED_BY=u2.userNo";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select distinct po.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from PUR_PO po
left join PUR_PO_ITEM poi on po.PO_NO=poi.PO_NO
left join BPMDB.dbo.PW_User u1 on po.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on po.CREATED_BY=u2.userNo
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            PoQueryPageResult result = new PoQueryPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.Pos = null;
                result.TotalCount = 0;
                return result;
            }
            List<PurPoEntity> pos = new List<PurPoEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                PurPoEntity po = new PurPoEntity(dr);
                pos.Add(po);
            }
            result.Pos = pos;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;
        }

        public PurPoItemEntity GetPurMaintainPoItemByKey(string purPoItemId)
        {
            if (string.IsNullOrEmpty(purPoItemId))
                return null;
            return DataAccess.SelectSingle(new PurPoItemEntity { Id = purPoItemId });
        }

        public List<PurPoItemEntity> GetPurMaintainPoItemAllListByPoNo(string poNo)
        {
            if (string.IsNullOrEmpty(poNo))
                return null;
            return DataAccess.Select<PurPoItemEntity>(new PurPoItemEntity { PoNo = poNo });
        }

        //采购到货计划
        public TransPurPoResult CreatePurArvByPo(PurPoEntity po)
        {
            PurArvEntity arv = new PurArvEntity();
            arv.Id = Guid.NewGuid().ToString();
            arv.PoNo = po.PoNo;
            arv.Status = Common.WfStatus.WFS10.ToString();
            List<PurPoItemEntity> poItems = PurPoItemBs.GetPoItemAllListByPoNo(po.PoNo);
            List<PurArvItemEntity> items = new List<PurArvItemEntity>();
            foreach (var poItem in poItems)
            {
                PurArvItemEntity arvItem = new PurArvItemEntity();
                arvItem.Id = Guid.NewGuid().ToString();
                arvItem.PoItemId = poItem.Id;
                arvItem.MaterialId = poItem.MaterialId;
                arvItem.MaterialName = poItem.MaterialName;
                arvItem.MaterialCode = poItem.MaterialCode;
                arvItem.Qty = poItem.Qty;
                arvItem.Unit = poItem.Unit;
                arvItem.DeliveryDate = poItem.DeliveryDate;
                items.Add(arvItem);
            }
            TransPurPoResult result = new TransPurPoResult();
            result.arv = arv;
            result.items = items;
            return result;
        }

        public List<PurArvEntity> CreatePurArvsByPo(List<PurPoEntity> pos)
        {
            throw new NotImplementedException();
        }

        public PurArvEntity AddPurArv(string userNo, PurArvEntity arv)
        {
            if (arv == null)
                return null;
            arv.CreatedBy = userNo;
            arv.CreatedDate = DateTime.Now;
            arv.UpdatedBy = userNo;
            arv.UpdatedDate = DateTime.Now;
            arv.ArvNo = MstCodeRuleBs.GetCurrentOrderCode("PurchaseArrivedOrder");
            if (DataAccess.Insert(arv))
                return arv;
            else return null;
        }

        public PurArvEntity UpdatePurArv(string userNo, PurArvEntity arv)
        {
            if (arv == null)
                return null;
            arv.UpdatedBy = userNo;
            arv.UpdatedDate = DateTime.Now;
            if (DataAccess.Update(arv))
                return arv;
            else return null;
        }

        public int? DelPurMaintainArv(PurArvEntity arv)
        {
            if (arv == null)
                return 1;
            if (arv.ArvNo == null)
                return 2;
            List<PurArvItemEntity> items = DataAccess.Select<PurArvItemEntity>(new PurArvItemEntity { ArvNo = arv.ArvNo });
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (items != null)
                        DataAccess.Delete(items, broker);
                    DataAccess.Delete(arv, broker);
                    broker.Commit();
                    return null;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 3;
                }
            }

        }

        public int? DelPurMaintainArvItem(PurArvItemEntity item)
        {
            if (item == null)
                return 1;
            if (DataAccess.Delete(item))
                return null;
            else return 2;
        }

        public int? AddPurMaintainArv(PurArvEntity arv, List<PurArvItemEntity> items)
        {
            if (arv == null || items == null)
                return 1;
            if (items.Count == 0)
                return 2;
            arv.CreatedDate = DateTime.Now;
            arv.UpdatedDate = DateTime.Now;
            arv.ArvNo = MstCodeRuleBs.GetCurrentOrderCode("PurchaseArrivedOrder");
            arv.Status = Common.PurchaseArvStatus.ARV10.ToString();
            foreach (var item in items)
            {
                item.ArvNo = arv.ArvNo;
            }
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    DataAccess.Insert(arv, broker);
                    DataAccess.Insert(items, broker);
                    broker.Commit();
                    return null;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 3;
                }
            }

        }

        public int? SavePurMaintainArv(PurArvEntity arv, List<PurArvItemEntity> items)
        {
            if (arv == null || items == null)
                return 1;
            if (items.Count == 0)
                return 2;
            arv.UpdatedDate = DateTime.Now;
            List<PurArvItemEntity> addItems = new List<PurArvItemEntity>();
            List<PurArvItemEntity> updateItems = new List<PurArvItemEntity>();
            foreach (var item in items)
            {
                PurArvItemEntity t = DataAccess.SelectSingle<PurArvItemEntity>(new PurArvItemEntity { Id = item.Id });
                if (t == null)//新增
                {
                    t.ArvNo = arv.ArvNo;
                    addItems.Add(t);
                }
                else//更新
                {
                    updateItems.Add(item);
                }
            }
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    DataAccess.Update(arv, broker);
                    DataAccess.Insert(addItems, broker);
                    DataAccess.Update(updateItems, broker);
                    broker.Commit();
                    return null;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 3;
                }
            }

        }

        public PurArvEntity GetPurMaintainArvByKey(string id)
        {
            if (string.IsNullOrEmpty(id))
                return null;
            return DataAccess.SelectSingle<PurArvEntity>(new PurArvEntity { Id = id });
        }

        public PurArvQueryPageResult GetPurMaintainArvByCondition(PurArvQueryCondition con)
        {
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.CreatedBy))
            {
                condition += " and arv.CREATED_BY='" + con.CreatedBy + "'";
            }
            else if (!string.IsNullOrEmpty(con.CreatedUser))
            {
                condition += " and u1.firstName like '%" + con.CreatedUser + "%'";
            }
            if (con.IsActive != null)
            {
                condition += " and arv.IS_ACTIVE=" + con.IsActive;
            }
            if (!string.IsNullOrEmpty(con.ArvNo))
            {
                condition += " and arv.ARV_NO='" + con.ArvNo + "'";
            }
            if (!string.IsNullOrEmpty(con.PoNo))
            {
                condition += " and arv.PO_NO='" + con.PoNo + "'";
            }
            if (con.SupplierId != null)
            {
                condition += " and arv.SUPPLIER_ID='" + con.SupplierId + "'";
            }
            else if (con.SupplierName != null)
            {
                condition += " and arv.SUPPLIER_NAME like '%" + con.SupplierName + "%'";
            }
            if (con.MaterialId != null)
            {
                condition += " and arvi.MATERIAL_ID='" + con.MaterialId + "'";
            }
            else if (con.MaterialName != null)
            {
                condition += " and arvi.MATERIAL_NAME like '%" + con.MaterialName + "%'";
            }
            if (con.Status != null)
            {
                condition += " and arv.STATUS='" + con.Status + "'";
            }

            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from PUR_ARV) as cnt
from PUR_ARV
where id not in (
    select top ({1}*({2}-1)) id from PUR_ARV order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select distinct arv.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) arv
left join PUR_ARV_ITEM arvi on arv.ARV_NO=arvi.ARV_NO 
left join BPMDB.dbo.PW_User u1 on arv.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on arv.CREATED_BY=u2.userNo";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select distinct arv.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from PUR_ARV arv
left join PUR_ARV_ITEM arvi on arv.ARV_NO=arvi.ARV_NO 
left join BPMDB.dbo.PW_User u1 on arv.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on arv.CREATED_BY=u2.userNo
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            PurArvQueryPageResult result = new PurArvQueryPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.Arvs = null;
                result.TotalCount = 0;
                return result;
            }
            List<PurArvEntity> arvs = new List<PurArvEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                PurArvEntity arv = new PurArvEntity(dr);
                arvs.Add(arv);
            }
            result.Arvs = arvs;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;
        }

        public PurArvItemEntity GetPurMaintainArvItemByKey(string id)
        {
            if (string.IsNullOrEmpty(id))
                return null;
            return DataAccess.SelectSingle<PurArvItemEntity>(new PurArvItemEntity { Id = id });
        }

        public List<PurArvItemEntity> GetPurMaintainArvItemAllListByArvNo(string arvNo)
        {
            if (string.IsNullOrEmpty(arvNo))
                return null;
            return DataAccess.Select<PurArvItemEntity>(new PurArvItemEntity { ArvNo = arvNo });

        }

        //退货单
        public int? AddPurRo(string userNo, PurRoEntity ro)
        {
            if (ro == null)
                return 1;
            ro.RoNo = MstCodeRuleBs.GetCurrentOrderCode("PurReturnOrder");
            ro.CreatedBy = userNo;
            ro.CreatedDate = DateTime.Now;
            ro.UpdatedBy = userNo;
            ro.UpdatedDate = DateTime.Now;
            if (DataAccess.Insert(ro))
                return null;
            else return 2;
        }

        public int? UpdatePurRo(string userNo, PurRoEntity ro)
        {
            if (ro == null)
                return 1;
            ro.UpdatedBy = userNo;
            ro.UpdatedDate = DateTime.Now;
            if (DataAccess.Update(ro))
                return null;
            else return 2;
        }

        public int? DelPurRo(PurRoEntity ro)
        {
            if (ro == null)
                return 1;
            List<PurRoItemEntity> roItems = DataAccess.Select<PurRoItemEntity>(new PurRoItemEntity { RoId = ro.Id });
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (roItems != null)
                    {

                        DataAccess.Delete(roItems, broker);
                    }
                    DataAccess.Delete(ro, broker);
                    broker.Commit();
                    return null;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 2;
                }
            }
        }

        public int? SavePurRo(PurRoSaveParam param)
        {
            if (param == null)
                return 1;
            if (param.ro == null)
                return 2;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    PurRoEntity cur = DataAccess.SelectSingle<PurRoEntity>(new PurRoEntity { Id = param.ro.Id });
                    if (cur == null)//新增
                    {
                        param.ro.CreatedDate = DateTime.Now;
                        param.ro.UpdatedDate = DateTime.Now;
                        param.ro.CreatedBy = param.curUserNo;
                        param.ro.UpdatedBy = param.curUserNo;
                        param.ro.RoNo = MstCodeRuleBs.GetCurrentOrderCode("PurReturnOrder");
                        foreach (var item in param.addItems)
                        {
                            item.RoNo = param.ro.RoNo;
                            item.RoId = param.ro.Id;
                        }
                        List<PurRoItemEntity> roItems = new List<PurRoItemEntity>();
                        foreach (var addItemDto in param.addItems)
                        {
                            Mapper.CreateMap<PurRoItemDto, PurRoItemEntity>();
                            var roItem = AutoMapper.Mapper.Map<PurRoItemDto, PurRoItemEntity>(addItemDto);
                            roItems.Add(roItem);
                        }

                        broker.BeginTransaction();
                        DataAccess.Insert(param.ro, broker);
                        DataAccess.Insert(roItems, broker);
                        broker.Commit();
                        return null;
                    }
                    else//修改
                    {
                        param.ro.UpdatedDate = DateTime.Now;
                        param.ro.UpdatedBy = param.curUserNo;
                        foreach (var item in param.addItems)
                        {
                            item.RoNo = param.ro.RoNo;
                            item.RoId = param.ro.Id;
                        }
                        List<PurRoItemEntity> delItems = new List<PurRoItemEntity>();
                        foreach (var delItemDto in param.delItems)
                        {
                            Mapper.CreateMap<PurRoItemDto, PurRoItemEntity>();
                            var roItem = AutoMapper.Mapper.Map<PurRoItemDto, PurRoItemEntity>(delItemDto);
                            delItems.Add(roItem);
                        }
                        List<PurRoItemEntity> updatedItems = new List<PurRoItemEntity>();
                        foreach (var updatedItemDto in param.updatedItems)
                        {
                            Mapper.CreateMap<PurRoItemDto, PurRoItemEntity>();
                            var roItem = AutoMapper.Mapper.Map<PurRoItemDto, PurRoItemEntity>(updatedItemDto);
                            updatedItems.Add(roItem);
                        }
                        List<PurRoItemEntity> addItems = new List<PurRoItemEntity>();
                        foreach (var addItemDto in param.addItems)
                        {
                            Mapper.CreateMap<PurRoItemDto, PurRoItemEntity>();
                            var roItem = AutoMapper.Mapper.Map<PurRoItemDto, PurRoItemEntity>(addItemDto);
                            addItems.Add(roItem);
                        }

                        broker.BeginTransaction();
                        DataAccess.Update(param.ro, broker);
                        DataAccess.Delete(delItems, broker);
                        DataAccess.Update(updatedItems, broker);
                        DataAccess.Insert(addItems, broker);
                        broker.Commit();
                        return null;
                    }
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return 3;
                }
            }
        }

        public PurRoPageResult GetPurRoAllListByCondition(PurRoQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.RoNo))
            {
                condition += " and ro.RO_NO='" + con.RoNo + "'";
            }
            if (!string.IsNullOrEmpty(con.PurPoNo))
            {
                condition += " and ro.PO_NO='" + con.PurPoNo + "'";
            }
            if (!string.IsNullOrEmpty(con.PurArvNo))
            {
                condition += " and ro.PUR_ARV_NO='" + con.PurArvNo + "'";
            }
            if (!string.IsNullOrEmpty(con.SupplierId))
            {
                condition += " and ro.SUPPLIER_ID='" + con.SupplierId + "'";
            }
            else if (!string.IsNullOrEmpty(con.SupplierName))
            {
                condition += " and ro.SUPPLIER_NAME like '%" + con.SupplierName + "%'";
            }
            condition += " and ro.IS_ACTIVE=" + con.IsActive;
            if (con.Status != null)
            {
                condition += " and ro.STATUS='" + con.Status + "'";
            }
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from PUR_RO) as cnt
from PUR_RO
where id not in (
    select top ({1}*({2}-1)) id from PUR_RO order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select ro.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) ro
left join BPMDB.dbo.PW_User u1 on ro.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on ro.CREATED_BY=u2.userNo";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select ro.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from PUR_RO ro
left join BPMDB.dbo.PW_User u1 on ro.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on ro.CREATED_BY=u2.userNo
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            PurRoPageResult result = new PurRoPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.ReturnOrders = null;
                result.TotalCount = 0;
                return result;
            }
            List<PurRoEntity> returnOrders = new List<PurRoEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                PurRoEntity ro = new PurRoEntity(dr);
                returnOrders.Add(ro);
            }
            result.ReturnOrders = returnOrders;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;

        }

        public PurRoEntity GetPurRoByKey(string roId)
        {
            if (string.IsNullOrEmpty(roId))
                return null;
            return DataAccess.SelectSingle<PurRoEntity>(new PurRoEntity { Id = roId });
        }

        //PurRoItem 采购退货单明细
        public List<PurRoItemDto> GetRoItemDtoAllListByRoId(string roId)
        {
            if (string.IsNullOrEmpty(roId))
            {
                return null;
            }
            var ro = DataAccess.SelectSingle<PurRoEntity>(new PurRoEntity { Id = roId });
            if (ro == null)
            {
                return null;
            }
            var roItems = DataAccess.Select<PurRoItemEntity>(new PurRoItemEntity { RoId = roId });
            if (roItems != null)
            {
                List<PurRoItemDto> roItemsDto = new List<PurRoItemDto>();
                foreach (var roItem in roItems)
                {
                    Mapper.CreateMap<PurRoItemEntity, PurRoItemDto>();
                    var roItemDto = AutoMapper.Mapper.Map<PurRoItemEntity, PurRoItemDto>(roItem);
                    DataSet ds = PurArvItemBs.GetArvItemQty(ro.PoNo, roItem.MaterialId);
                    if (ds != null && ds.Tables[0].Rows.Count != 0 && ds.Tables[0].Rows[0]["ARV_TOTAL_QTY"] != DBNull.Value)
                    {
                        roItemDto.ArvQty = (decimal)ds.Tables[0].Rows[0]["ARV_TOTAL_QTY"];
                    }
                    var poItem = DataAccess.SelectSingle<PurPoItemEntity>(new PurPoItemEntity { PoNo = ro.PoNo, MaterialId = roItem.MaterialId });
                    if (poItem != null)
                    {
                        roItemDto.InvInQty = poItem.AcpQty;
                    }
                    roItemsDto.Add(roItemDto);
                }
                return roItemsDto;
            }
            else
            {
                return null;
            }
        }

        //PurPoItem 采购单明细
        public PurPoItemPageResult GetPoItemAllListByCondition(PurPoItemQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.poNo))
            {
                condition += " and item.PO_NO='" + con.poNo + "'";
            }
            if (!string.IsNullOrEmpty(con.MaterialId))
            {
                condition += " and item.MATERIAL_ID='" + con.MaterialId + "'";
            }
            else if (!string.IsNullOrEmpty(con.MaterialName))
            {
                condition += " and item.MATERIAL_NAME like '%" + con.MaterialName + "%'";
            }
            if (con.poStatus != null)
            {
                condition += " and PO.STATUS='" + con.poStatus + "'";
            }
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from PUR_PO_ITEM) as cnt
from PUR_PO_ITEM
where id not in (
    select top ({1}*({2}-1)) id from PUR_PO_ITEM)";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select item.*
from ({0}) item
left join PUR_PO po on item.PO_NO=po.PO_NO";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t)";
                string sqlSource = @"select item.*
from PUR_PO_ITEM item
left join PUR_PO po on item.PO_NO=po.PO_NO
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            PurPoItemPageResult result = new PurPoItemPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.PoItemsDto = null;
                result.TotalCount = 0;
                return result;
            }
            List<PurPoItemDto> itemsDto = new List<PurPoItemDto>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                PurPoItemEntity item = new PurPoItemEntity(dr);
                Mapper.CreateMap<PurPoItemEntity, PurPoItemDto>();
                var ItemDto = AutoMapper.Mapper.Map<PurPoItemEntity, PurPoItemDto>(item);

                itemsDto.Add(ItemDto);
            }
            result.PoItemsDto = itemsDto;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;

        }

        //供应商范围
        public List<MstSupplierScopeEntity> GetPurSupplierScopeAllListBySupplierId(string supplierId)
        {
            if (string.IsNullOrEmpty(supplierId))
                return null;
            return DataAccess.Select<MstSupplierScopeEntity>(new MstSupplierScopeEntity { SupplierId = supplierId });

        }

        //工作流：采购到货-质检
        public List<QcIqcEntity> PurArvToQcIqc(string userNo, PurArvEntity arv)
        {
            if (arv == null)
                return null;
            List<PurArvItemEntity> arvItems = new List<PurArvItemEntity>();
            var items1 = DataAccess.Select<PurArvItemEntity>(new PurArvItemEntity { ArvNo = arv.ArvNo, InspectionType = InspectionType.ISPC20.ToString() });
            var items2 = DataAccess.Select<PurArvItemEntity>(new PurArvItemEntity { ArvNo = arv.ArvNo, InspectionType = InspectionType.ISPC30.ToString() });
            arvItems.AddRange(items1);
            arvItems.AddRange(items2);
            List<QcIqcEntity> iqcs = new List<QcIqcEntity>();
            foreach (var arvItem in arvItems)
            {
                QcIqcEntity iqc = new QcIqcEntity();
                iqc.Id = Guid.NewGuid().ToString();
                iqc.CreatedBy = userNo;
                iqc.CreatedDate = DateTime.Now;
                iqc.UpdatedBy = userNo;
                iqc.UpdatedDate = DateTime.Now;
                iqc.IsActive = 1;
                iqc.IqcNo = MstCodeRuleBs.GetCurrentOrderCode("QC_IQC");
                iqc.PurArvNo = arv.ArvNo;
                iqc.PoNo = arv.PoNo;
                iqc.MaterialId = arvItem.MaterialId;
                iqc.MaterialName = arvItem.MaterialName;
                iqc.MaterialCode = arvItem.MaterialCode;
                iqc.Qty = arvItem.ActuralArvQty;
                if (arvItem.InspectionType.Equals(InspectionType.ISPC20.ToString()))
                {
                    iqc.Type = "QCF20";
                }
                if (arvItem.InspectionType.Equals(InspectionType.ISPC30.ToString()))
                {
                    iqc.Type = "QCF30";
                }
                iqc.Status = WfStatus.WFS20.ToString();
                iqcs.Add(iqc);
            }
            if (DataAccess.Insert(iqcs))
                return iqcs;
            else return null;
        }

        public InvIappEntity PurArvToInvIapp(string userNo, PurArvEntity arv)
        {
            if (arv == null)
                return null;
            List<PurArvItemEntity> arvItems = DataAccess.Select<PurArvItemEntity>(new PurArvItemEntity { ArvNo = arv.ArvNo, InspectionType = InspectionType.ISPC10.ToString() });
            InvIappEntity iapp = new InvIappEntity();
            List<InvIappiEntity> iappItems = new List<InvIappiEntity>();

            iapp.Id = Guid.NewGuid().ToString();
            iapp.CreatedBy = userNo;
            iapp.CreatedDate = DateTime.Now;
            iapp.UpdatedBy = userNo;
            iapp.UpdatedDate = DateTime.Now;
            iapp.IsActive = 1;
            iapp.AppNo = MstCodeRuleBs.GetCurrentOrderCode("InvInAppOrder");
            iapp.Type = (int)InvInType.采购到货入库;
            iapp.RelatedId = arv.Id;
            iapp.Status = WfStatus.WFS20.ToString();
            foreach (var arvItem in arvItems)
            {
                InvIappiEntity iappItem = new InvIappiEntity();
                iappItem.Id = Guid.NewGuid().ToString();
                iappItem.IoAppId = iapp.Id;
                iappItem.ProductId = arvItem.MaterialId;
                iappItem.Qty = arvItem.ActuralArvQty;
                iappItems.Add(iappItem);
            }
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    DataAccess.Insert(iapp, broker);
                    DataAccess.Insert(iappItems, broker);
                    broker.Commit();
                    return iapp;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return null;
                }
            }

        }


        //采购退货-出库申请
        public InvOappEntity PurRoToInvOapp(string userNo, PurRoEntity ro)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    ro.UpdatedBy = userNo;
                    ro.UpdatedDate = DateTime.Now;
                    ro.Status = WfStatus.WFS20.ToString();

                    InvOappEntity oapp = new InvOappEntity();
                    oapp.Id = Guid.NewGuid().ToString();
                    oapp.CreatedBy = userNo;
                    oapp.CreatedDate = DateTime.Now;
                    oapp.UpdatedBy = userNo;
                    oapp.UpdatedDate = DateTime.Now;
                    oapp.IsActive = 1;
                    oapp.AppNo = MstCodeRuleBs.GetCurrentOrderCode("InvOutAppOrder");
                    oapp.Type = (int)InvOutType.采购退货出库;
                    oapp.RelatedId = ro.Id;
                    oapp.RelatedNo = ro.RoNo;
                    oapp.Status = WfStatus.WFS20.ToString();
                    oapp.Remark = "工作流：采购退货";

                    List<PurRoItemEntity> roItems = DataAccess.Select<PurRoItemEntity>(new PurRoItemEntity { RoId = ro.Id });
                    List<InvOappiEntity> oappItems = new List<InvOappiEntity>();
                    if (roItems != null)
                    {
                        foreach (var roItem in roItems)
                        {
                            InvOappiEntity oappItem = new InvOappiEntity();
                            oappItem.Id = Guid.NewGuid().ToString();
                            oappItem.OoAppId = oapp.Id;
                            oappItem.ProductId = roItem.MaterialId;
                            oappItem.Qty = roItem.Qty;
                            oappItems.Add(oappItem);
                        }
                    }

                    broker.BeginTransaction();
                    DataAccess.Update(ro, broker);
                    DataAccess.Insert(oapp, broker);
                    DataAccess.Insert(oappItems, broker);
                    broker.Commit();
                    return oapp;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return null;
                }
            }
        }

        //查询采购汇总数据
        public PurSupplierSummeryPageResult GetPurSupplierSummary(SupplierQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.SupplierCode))
            {
                condition += " and sup.SUPPLIER_CODE='" + con.SupplierCode + "'";
            }
            else if (!string.IsNullOrEmpty(con.SupplierName))
            {
                condition += " and sup.SUPPLIER_NAME like '%" + con.SupplierName + "%'";
            }
            if (!string.IsNullOrEmpty(con.SupplierType))
            {
                condition += " and scope.SUPPLIER_SCOPE_NO='" + con.SupplierType + "'";
            }
            if (con.CreatedDate != null && con.CreatedDate != new DateTime(1, 1, 1))
            {
                condition += " and po.CREATED_DATE>='"+con.CreatedDate.ToString("yyyy-MM-dd HH:mm:ss")+"'";
            }
            if (con.CreatedDateTo != null && con.CreatedDateTo != new DateTime(1, 1, 1))
            {
                condition += " and po.CREATED_DATE<='" + con.CreatedDate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (!string.IsNullOrEmpty(con.Status))
            {
                condition += " and sup.STATUS='" + con.Status + "'";
            }

            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from MST_SUPPLIER) as cnt
from MST_SUPPLIER
where id not in (
    select top ({1}*({2}-1)) id from MST_SUPPLIER order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select sup.* from ({0}) sup";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select distinct sup.* from MST_SUPPLIER sup
left join PUR_PO po on sup.ID=po.SUPPLIER_ID
left join MST_SUPPLIER_SCOPE scope on sup.ID=scope.SUPPLIER_ID where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            PurSupplierSummeryPageResult result = new PurSupplierSummeryPageResult();
            List<PurSupplierSummaryDto> dtos = new List<PurSupplierSummaryDto>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                MstSupplierEntity sup = new MstSupplierEntity(dr);
                Mapper.CreateMap<MstSupplierEntity, PurSupplierSummaryDto>();
                var supDto = AutoMapper.Mapper.Map<MstSupplierEntity, PurSupplierSummaryDto>(sup);
                List<PurPoEntity> poList = DataAccess.Select<PurPoEntity>(new PurPoEntity { SupplierId = supDto.Id });
                supDto.PoCount = poList.Count;
                supDto.UnclosedPoCount = poList.Where(a => a.Status == WfStatus.WFS20.ToString()).Count() + poList.Where(a => a.Status == WfStatus.WFS10.ToString()).Count();
                supDto.PoTotalAmount = poList.Where(a => a.Status == WfStatus.WFS20.ToString()).Sum(a => a.TotalAmount) + poList.Where(a => a.Status == WfStatus.WFS30.ToString()).Sum(a => a.TotalAmount);
                supDto.InputAmount = 0;
                supDto.PaymentsMade = 0;
                supDto.AmountDue = 0;
                supDto.InvoiceAmount = 0;
                supDto.UninvoiceAmount = 0;
                dtos.Add(supDto);

            }
            result.SummaryDto=dtos;
            result.TotalCount=dtos.Count;
            return result;
        }

        //查询采购业务清单
        public PurBusinessListDtoPageResult GetPurBusinessList(PurBusinessListQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.SupplierNo))
            {
                condition += " and po.SUPPLIER_CODE='" + con.SupplierNo + "'";
            }
            else if (!string.IsNullOrEmpty(con.PoNo))
            {
                condition += " and item.PO_NO='%" + con.PoNo + "%'";
            }
            if (!string.IsNullOrEmpty(con.QuNo))
            {
                condition += " and po.QU_NO='" + con.QuNo + "'";
            }
            if (!string.IsNullOrEmpty(con.ItemCode))
            {
                condition += " and po.MATERIAL_CODE='" + con.ItemCode + "'";
            }
            if (!string.IsNullOrEmpty(con.PurchaseType))
            {
                condition += " and po.PURCHASE_TYPE='" + con.PurchaseType + "'";
            }
            if (con.poDate != null && con.poDate!=new DateTime(1,1,1))
            {
                condition += " and po.CREATED_DATE>='" + con.poDate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (con.poDateTo != null && con.poDateTo != new DateTime(1, 1, 1))
            {
                condition += " and po.CREATED_DATE<='" + con.poDateTo.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }

            if (con.DeliveryDate != null && con.DeliveryDate != new DateTime(1, 1, 1))
            {
                condition += " and item.DELIVERY_DATE>='" + con.DeliveryDate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (con.DeliveryDateTo != null && con.DeliveryDateTo != new DateTime(1, 1, 1))
            {
                condition += " and item.DELIVERY_DATE<='" + con.DeliveryDateTo.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (!string.IsNullOrEmpty(con.poStatus))
            {
                condition += " and po.STATUS='" + con.poStatus + "'";
            }
            condition += " and po.IS_ACTIVE=" + con.IsActive;

            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from PUR_PO_ITEM) as cnt
from PUR_PO_ITEM
where id not in (
    select top ({1}*({2}-1)) id from PUR_PO_ITEM order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select item.* from ({0}) item";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select distinct item.*,po.QU_NO,po.SUPPLIER_CONTACT,po.SUPPLIER_TEL 
from PUR_PO_ITEM item
left join PUR_PO po on item.PO_NO=po.PO_NO
left join MST_SUPPLIER_SCOPE scope on sup.ID=scope.SUPPLIER_ID where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            PurBusinessListDtoPageResult result = new PurBusinessListDtoPageResult();
            List<PurBusinessListDto> dtos = new List<PurBusinessListDto>();
            foreach(DataRow dr in ds.Tables[0].Rows)
            {
                PurPoItemEntity item = new PurPoItemEntity(dr);
                Mapper.CreateMap<PurPoItemEntity, PurBusinessListDto>();
                PurBusinessListDto dto = Mapper.Map<PurPoItemEntity, PurBusinessListDto>(item);
                dto.SelectedFlag = 0;
                if (dr["QU_NO"]!=DBNull.Value)
                    dto.QuNo = dr["QU_NO"].ToString();
                if (dr["SUPPLIER_CONTACT"] != DBNull.Value)
                    dto.ContactPerson = dr["SUPPLIER_CONTACT"].ToString();
                if (dr["SUPPLIER_TEL"] != DBNull.Value)
                    dto.ContactTel = dr["SUPPLIER_TEL"].ToString();
                dtos.Add(dto);

            }
            result.BusinessListDto = dtos;
            result.TotalCount = dtos.Count;

            return result;
        }
        
        //MRP 2014-10-08 xubenhua
        public DTODataTable GetPOArvAndStockInDetail()
        {
            DTODataTable output = new DTODataTable();
            try
            {
                DataTable ds = PurPoBs.GetPOArvAndStockInDetail();
                output.result = ds;

            }
            catch (Exception ex)
            {
                output.ErrorCode = "GetPOArvAndStockInDetail";
                output.ErrorMessage = ex.Message;
            }
            return output;
        }

        public DTODataTable GetPRNotToPo()
        {
            DTODataTable output = new DTODataTable();
            try
            {
                DataTable ds = PurPoBs.GetPRNotToPo();
                output.result = ds;

            }
            catch (Exception ex)
            {
                output.ErrorCode = "GetPRNotToPo";
                output.ErrorMessage = ex.Message;
            }
            return output;
        }

        public DTOBool CreatePRByPO(PurAppEntity pr, List<PurAppItemEntity> prItem)
        {
            DTOBool output = new DTOBool();
            try
            {
                output.result = PurAppBs.CreatePRByPO(pr, prItem);

            }
            catch (Exception ex)
            {
                output.ErrorCode = "CreatePRByPO";
                output.ErrorMessage = ex.Message;
            }
            return output;
        }

    }
}

